﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Customer_Profile.eCardReport.DBDao;
using KVB.Common;
using System.Text;
using Customer_Profile.eCard.DBDao;
using DevExpress.Data;
using DevExpress.Web.ASPxGridView;

namespace Customer_Profile.eCardReport
{
    public partial class EntitybyMonthReprot : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet dsProd = ProductDao.getAllProduct("ALL");
            DevExpress.Web.ASPxEditors.ASPxListBox listbox = (DevExpress.Web.ASPxEditors.ASPxListBox)ASPxDropDownEdit1.FindControl("listBox");
            listbox.DataSource = dsProd;
            listbox.TextField = "procduct_name";
            listbox.ValueField = "procduct_code";
            listbox.DataBind();

            DevExpress.Web.ASPxEditors.ListEditItem li = new DevExpress.Web.ASPxEditors.ListEditItem("选择全部");
            listbox.Items.Insert(0, li);    
            if (!Page.IsPostBack)
            {
                //DataSet dsProd = ProductDao.getAllProduct("ALL");
                //dropProduct.DataSource = dsProd;
                //dropProduct.DataTextField = "procduct_name";
                //dropProduct.DataValueField = "procduct_code";
                //dropProduct.DataBind();
                //ListItem liAll = new ListItem();
                //liAll.Text = "All";
                //liAll.Value = "All";
                //dropProduct.Items.Insert(0, liAll);

                string strYear = DateTime.Now.Year.ToString();
                dropYear.Items.FindByValue(strYear).Selected = true;
                Comm.BindDll(ref dateType, "dateType", false, "");
                //string strProdCode = "";
                //for (int i = 0; i < dsProd.Tables[0].Rows.Count; i++)
                //{
                //    strProdCode += dsProd.Tables[0].Rows[i]["procduct_code"].ToString() + ";";
                //}
                //strProdCode = strProdCode.Substring(0, strProdCode.Length - 1);
                //ASPxDropDownEdit1.Text = strProdCode;
            }
            BindDataView();
            ASPxGridView1.GroupSummarySortInfo.Clear();
            ColumnSortOrder sortOrder = ColumnSortOrder.Descending;
            ASPxGridView1.GroupSummarySortInfo.AddRange(new ASPxGroupSummarySortInfo("entity", ASPxGridView1.GroupSummary["initial_amount"], sortOrder));
            int colCount = ASPxGridView1.Columns.Count;
            for (int i = 1;i < colCount; i++ )
            {
                ((GridViewDataColumn)ASPxGridView1.Columns[i]).PropertiesEdit.DisplayFormatString = "c";
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {

        }

        private void BindDataView()
        {
            string startDate = dropYear.SelectedItem.Value + "-01-01";
            string endDate = dropYear.SelectedItem.Value + "-12-31";
            string strSQL = "";
            string strSQL1 = "";
            string strSQL2 = "";
            try
            {
                #region
                /*
                select entity,
                SUM(incard_count) as 'Total:',
                SUM(case when convert(date,value_date) between '2012-01-01' and '2012-01-31' then incard_count else 0 end ) '一月' ,
                SUM(case when convert(date,value_date) between '2012-02-01' and '2012-02-29' then incard_count else 0 end ) '二月' ,
                SUM(case when convert(date,value_date) between '2012-03-01' and '2012-03-31' then incard_count else 0 end ) '三月' ,
                SUM(case when convert(date,value_date) between '2012-04-01' and '2012-04-30' then incard_count else 0 end ) '四月' ,
                SUM(case when convert(date,value_date) between '2012-05-01' and '2012-05-31' then incard_count else 0 end ) '五月' ,
                SUM(case when convert(date,value_date) between '2012-06-01' and '2012-06-30' then incard_count else 0 end ) '六月' ,
                SUM(case when convert(date,value_date) between '2012-07-01' and '2012-07-31' then incard_count else 0 end ) '七月',
                SUM(case when convert(date,value_date) between '2012-08-01' and '2012-08-31' then incard_count else 0 end ) '八月',
                SUM(case when convert(date,value_date) between '2012-09-01' and '2012-09-30' then incard_count else 0 end ) '九月',
                SUM(case when convert(date,value_date) between '2012-10-01' and '2012-10-31' then incard_count else 0 end ) '十月',
                SUM(case when convert(date,value_date) between '2012-11-01' and '2012-11-30' then incard_count else 0 end ) '十一月',
                SUM(case when convert(date,value_date) between '2012-12-01' and '2012-12-31' then incard_count else 0 end ) '十二月'  
                from mmdeals 
                where convert(date,value_date) between '2012-01-01' and '2012-12-31' and prod_code='T12' and if_refund='N' 
                group by entity
                */
                #endregion

                strSQL1 = "";
                strSQL2 = "";
                DataSet dsRefDate = ReportDao.getPressureRefDate(startDate, endDate);
                strSQL1 += "select entity,sum(initial_amount) as 'total',";
                strSQL2 += "select 'total' as entity ,sum(initial_amount) as 'total',";
                foreach (DataRow dr in dsRefDate.Tables[0].Rows)
                {
                    if (dateType.SelectedItem.Value.Trim().Equals("review_date"))
                    {
                        strSQL1 += "SUM(case when convert(date,review_date) between '" + dr["ref_start_date"] + "' and '" + dr["ref_end_date"] + "' then initial_amount else 0 end ) '" + dr["ref_remark_date"] + "' ,";
                        strSQL2 += "SUM(case when convert(date,review_date) between '" + dr["ref_start_date"] + "' and '" + dr["ref_end_date"] + "' then initial_amount else 0 end ) '" + dr["ref_remark_date"] + "' ,";
                    }
                    else if(dateType.SelectedItem.Value.Trim().Equals("value_date"))
                    {
                        strSQL1 += "SUM(case when convert(date,value_date) between '" + dr["ref_start_date"] + "' and '" + dr["ref_end_date"] + "' then initial_amount else 0 end ) '" + dr["ref_remark_date"] + "' ,";
                        strSQL2 += "SUM(case when convert(date,value_date) between '" + dr["ref_start_date"] + "' and '" + dr["ref_end_date"] + "' then initial_amount else 0 end ) '" + dr["ref_remark_date"] + "' ,";
                    }
                }
                strSQL1 = strSQL1.Substring(0, strSQL1.Length - 1);
                strSQL2 = strSQL2.Substring(0, strSQL2.Length - 1);

                string strProd = "";
                if (!ASPxDropDownEdit1.Text.Trim().Equals(""))
                {
                    strProd = " And prod_code in (select STR1 from dbo.fn_SPLITSTR('" + ASPxDropDownEdit1.Text.Trim() + "',';')) ";
                }

                if (dateType.SelectedItem.Value.Trim().Equals("review_date"))
                {
                    strSQL1 += " from mmdeals where convert(date,review_date) between '" + startDate + "' and '" + endDate + "'  and  status='PASS' AND if_returncard='N' " + strProd + " group by entity";
                    strSQL2 += " from mmdeals where convert(date,review_date) between '" + startDate + "' and '" + endDate + "'  and  status='PASS' AND if_returncard='N' "+ strProd ;
                }
                else if (dateType.SelectedItem.Value.Trim().Equals("value_date"))
                {
                    strSQL1 += " from mmdeals where convert(date,value_date) between '" + startDate + "' and '" + endDate + "'  and  status='PASS' AND if_returncard='N' " + strProd + " group by entity";
                    strSQL2 += " from mmdeals where convert(date,value_date) between '" + startDate + "' and '" + endDate + "'  and  status='PASS' AND if_returncard='N' " + strProd;
                }
                               
                strSQL =strSQL2 +" union all "+ strSQL1;
                //KVB.Common.Log.WriteDefaultLog("SQL:", strSQL);
                DataSet dsResult = ReportDao.DairySumReport(strSQL);
                if (dsResult != null && dsResult.Tables.Count > 0)
                {
                    ASPxGridView1.SettingsPager.PageSize = 40;
                    ASPxGridView1.Columns.Clear(); //清除列
                    ASPxGridView1.AutoGenerateColumns = true; //设置自动添加列
                    ASPxGridView1.DataSourceID = null;
                    ASPxGridView1.DataSource = dsResult.Tables[0];
                    ASPxGridView1.DataBind();
                    ASPxGridView1.AutoGenerateColumns = false;
                }
            }
            catch (Exception ex)
            {
                Log.WriteDefaultLog("Maturity_BindDataView", ex.ToString());
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            gridExport.WriteXlsToResponse();
        }
    }
}